insert overwrite table jms_dwd.dwd_tmsnew_shipment_stop_union_base_dt partition (dt)
select
    t1.id
     ,t1.shipment_id
     ,t1.shipment_no
     ,t1.sort_num
     ,t1.network_id
     ,t1.network_code
     ,t1.network_name
     ,t1.roadsection_id
     ,t1.planned_arrival_day
     ,t1.planned_arrival_hour
     ,t1.planned_arrival_time
     ,t1.actual_arrival_time
     ,t1.planned_departure_day
     ,t1.planned_departure_hour
     ,t1.planned_departure_time
     ,t1.actual_departure_time
     ,t1.stop_time
     ,t1.usetime
     ,t1.mileage
     ,t1.speed
     ,t1.batch_time
     ,t1.handling_type
     ,t1.load_start_time
     ,t1.load_end_time
     ,t1.unload_start_time
     ,t1.unload_end_time
     ,t1.app_arrival_time
     ,t1.app_departure_time
     ,t1.gps_arrival_time
     ,t1.gps_departure_time
     ,t1.lock_time
     ,t1.unlock_time
     ,t1.weight_arrival_time
     ,t1.weight_departure_time
     ,t1.arrival_gross_weight
     ,t1.arrival_tare_weight
     ,t1.arrival_net_weight
     ,t1.departure_gross_weight
     ,t1.departure_tare_weight
     ,t1.departure_net_weight
     ,t1.estimate_arrival_time
     ,t1.plan_driving_time
     ,t1.actual_driving_time
     ,t1.actual_arrival_time_source
     ,t1.actual_departure_time_source
     ,t1.estimate_batch_time
     ,t1.actual_batch_time
     ,t1.create_by
     ,t1.create_by_name
     ,t1.create_time
     ,t1.update_by
     ,t1.update_by_name
     ,t1.update_time
     ,t1.simple_name
     ,t1.latest_arrival_time
     ,t1.latest_arrival_hour
     ,t1.latest_arrival_day
     ,t1.gps_depart_mileage
     ,t1.gps_arrival_mileage
     ,t1.is_send
     ,null as financial_center_id
     ,null as financial_center_code
     ,null as financial_center_desc
     ,null as clock_time
     ,1    as table_type
     ,t2.shipment_name
     ,t2.shipment_type
     ,t2.grade
     ,t2.time_effective_shift
     ,t2.t_dt
from (
         select
             id
              ,shipment_id
              ,shipment_no
              ,sort_num
              ,network_id
              ,network_code
              ,network_name
              ,roadsection_id
              ,planned_arrival_day
              ,planned_arrival_hour
              ,planned_arrival_time
              ,actual_arrival_time
              ,planned_departure_day
              ,planned_departure_hour
              ,planned_departure_time
              ,actual_departure_time
              ,stop_time
              ,usetime
              ,mileage
              ,speed
              ,batch_time
              ,handling_type
              ,load_start_time
              ,load_end_time
              ,unload_start_time
              ,unload_end_time
              ,app_arrival_time
              ,app_departure_time
              ,gps_arrival_time
              ,gps_departure_time
              ,lock_time
              ,unlock_time
              ,weight_arrival_time
              ,weight_departure_time
              ,arrival_gross_weight
              ,arrival_tare_weight
              ,arrival_net_weight
              ,departure_gross_weight
              ,departure_tare_weight
              ,departure_net_weight
              ,estimate_arrival_time
              ,plan_driving_time
              ,actual_driving_time
              ,actual_arrival_time_source
              ,actual_departure_time_source
              ,estimate_batch_time
              ,actual_batch_time
              ,create_by
              ,create_by_name
              ,create_time
              ,update_by
              ,update_by_name
              ,update_time
              ,simple_name
              ,latest_arrival_time
              ,latest_arrival_hour
              ,latest_arrival_day
              ,gps_depart_mileage
              ,gps_arrival_mileage
              ,is_send
         from jms_ods.yl_tmsnew_tms_shipment_stop
         where dt = '{{ execution_date | cst_ds }}'
           and to_date(create_time) between date_sub('{{ execution_date | cst_ds }}', 100) and '{{ execution_date | cst_ds }}'
     ) t1 join(
    select shipment_no as s_shipment_no,shipment_name,shipment_type, to_date(planned_departure_time) as t_dt,grade,time_effective_shift
    from jms_ods.yl_tmsnew_tms_shipment
    where dt = '{{ execution_date | cst_ds }}'
      and to_date(create_time) between date_sub('{{ execution_date | cst_ds }}', 100) and '{{ execution_date | cst_ds }}'
      and to_date(planned_departure_time) between date_sub('{{ execution_date | cst_ds }}', 89) and date_add('{{ execution_date | cst_ds }}',7)
) t2 on t1.shipment_no = t2.s_shipment_no
union all
select
    t1.id
     ,t1.shipment_id
     ,t1.shipment_no
     ,t1.sort_num
     ,t1.network_id
     ,t1.network_code
     ,t1.network_name
     ,t1.roadsection_id
     ,t1.planned_arrival_day
     ,t1.planned_arrival_hour
     ,t1.planned_arrival_time
     ,t1.actual_arrival_time
     ,t1.planned_departure_day
     ,t1.planned_departure_hour
     ,t1.planned_departure_time
     ,t1.actual_departure_time
     ,t1.stop_time
     ,t1.usetime
     ,t1.mileage
     ,t1.speed
     ,t1.batch_time
     ,t1.handling_type
     ,t1.load_start_time
     ,t1.load_end_time
     ,t1.unload_start_time
     ,t1.unload_end_time
     ,t1.app_arrival_time
     ,t1.app_departure_time
     ,t1.gps_arrival_time
     ,t1.gps_departure_time
     ,t1.lock_time
     ,t1.unlock_time
     ,t1.weight_arrival_time
     ,t1.weight_departure_time
     ,t1.arrival_gross_weight
     ,t1.arrival_tare_weight
     ,t1.arrival_net_weight
     ,t1.departure_gross_weight
     ,t1.departure_tare_weight
     ,t1.departure_net_weight
     ,t1.estimate_arrival_time
     ,t1.plan_driving_time
     ,t1.actual_driving_time
     ,t1.actual_arrival_time_source
     ,t1.actual_departure_time_source
     ,t1.estimate_batch_time
     ,t1.actual_batch_time
     ,t1.create_by
     ,t1.create_by_name
     ,t1.create_time
     ,t1.update_by
     ,t1.update_by_name
     ,t1.update_time
     ,t1.simple_name
     ,null as latest_arrival_time
     ,null as latest_arrival_hour
     ,null as latest_arrival_day
     ,t1.gps_depart_mileage
     ,t1.gps_arrival_mileage
     ,null as is_send
     ,t1.financial_center_id
     ,t1.financial_center_code
     ,t1.financial_center_desc
     ,t1.clock_time
     ,2 as table_type
     ,t2.shipment_name
     ,'' as shipment_type
     ,null as grade
     ,null as time_effective_shift
     ,t2.t_dt
from (
         select
             id
              ,shipment_id
              ,shipment_no
              ,sort_num
              ,network_id
              ,network_code
              ,network_name
              ,roadsection_id
              ,planned_arrival_day
              ,planned_arrival_hour
              ,planned_arrival_time
              ,actual_arrival_time
              ,planned_departure_day
              ,planned_departure_hour
              ,planned_departure_time
              ,actual_departure_time
              ,stop_time
              ,usetime
              ,mileage
              ,speed
              ,batch_time
              ,handling_type
              ,load_start_time
              ,load_end_time
              ,unload_start_time
              ,unload_end_time
              ,app_arrival_time
              ,app_departure_time
              ,gps_arrival_time
              ,gps_departure_time
              ,lock_time
              ,unlock_time
              ,weight_arrival_time
              ,weight_departure_time
              ,arrival_gross_weight
              ,arrival_tare_weight
              ,arrival_net_weight
              ,departure_gross_weight
              ,departure_tare_weight
              ,departure_net_weight
              ,estimate_arrival_time
              ,plan_driving_time
              ,actual_driving_time
              ,actual_arrival_time_source
              ,actual_departure_time_source
              ,estimate_batch_time
              ,actual_batch_time
              ,create_by
              ,create_by_name
              ,create_time
              ,update_by
              ,update_by_name
              ,update_time
              ,simple_name
              ,gps_depart_mileage
              ,gps_arrival_mileage
              ,financial_center_id
              ,financial_center_code
              ,financial_center_desc
              ,clock_time
         from jms_ods.yl_tmsnew_branch_shipment_stop
         where dt = '{{ execution_date | cst_ds }}'
     ) t1 join (
    select shipment_no as s_shipment_no,shipment_name, to_date(planned_departure_time) as t_dt
    from jms_ods.yl_tmsnew_branch_shipment
    where dt = '{{ execution_date | cst_ds }}'
      and to_date(planned_departure_time) between date_sub('{{ execution_date | cst_ds }}',89) and date_add('{{ execution_date | cst_ds }}',7)
) t2 on t1.shipment_no = t2.s_shipment_no
distribute by t2.t_dt;